<html>
<body>
<?php
        /*      This script should be used to create clean tables.
                If this script is ran after data has been inserted into the tables
                that data will be lost.
        */

    /* Database information */
    include_once("../submit/pword.php");
    
	/* Files with column names */
	$files = array();
    $files[] = "DiseaseTablesExplained.txt";
	$files[] = "ClimateTablesExplained.txt";
	$files[] = "CensusTablesExplained.txt";
	
	/* Connect to the database */
	mysql_connect("$server", "$username", "$password") or die(mysql_error()); 
	mysql_select_db("cs1630History") or die(mysql_error()); 
	
	/* Drop table if needed */
	$result = mysql_query ("DROP TABLE IF EXISTS column_names") or die ("column_names Invalid: " . mysql_error());
	/* Create table */
	$result = mysql_query(
			"CREATE TABLE column_names (
				name char(35) primary key not null, 
				long_name char(255) not null)")
	or die ("column_names Invalid: " . mysql_error());
	
	/* Drop table if needed */
	$result = mysql_query ("DROP TABLE IF EXISTS dataset_master") or die ("dataset_master Invalid: " . mysql_error());
	/* Create table */
	$result = mysql_query(
			"CREATE TABLE dataset_master (
				_ID int primary key not null auto_increment, 
				name char(255) not null)")
	or die ("dataset_master Invalid: " . mysql_error());
	
	/* Drop table if needed */
	$result = mysql_query ("DROP TABLE IF EXISTS locations") or die ("locations Invalid: " . mysql_error());
	/* Create table */
	$result = mysql_query(
			"CREATE TABLE locations (
				_ID int primary key not null auto_increment,
				country char(255),
				state char(255),
				county char(255),
				city char(255))")
	or die ("locations Invalid: " . mysql_error());
	
	foreach ($files as $file){
		/* Get line from file */
		$line = file("$file", FILE_IGNORE_NEW_LINES) or die("File not found!");
		
		/* Loop through each row in file */
		foreach($line as $row)
		{
			/* Get the table name */
			$table = explode(",", addslashes(rtrim($row)));
		   
			/* Drop table if needed */
			$result = mysql_query ("DROP TABLE IF EXISTS $table[0]") or die ("$table[0] Invalid: " . mysql_error()); 
			
			/* Create table */
			$result = mysql_query(
					"CREATE TABLE $table[0] (
						_ID int primary key not null auto_increment, 
						start_date date not null,
						end_date date not null,
						pub_date date not null,
						frequency_unit char(30) not null,
						frequency_quantity int not null,
						location int not null,
						col_ID int not null,
						dataset_ID int not null,
						value float not null, 
						unit_numerator_type char(35) not null,
						unit_numerator_value int not null,
						unit_denominator_type char(35) not null,
						unit_denominator_value int not null,    
						is_real boolean not null,
						is_obsolete boolean not null)")
			or die ("$table[0] Invalid: " . mysql_error());
			
			/* Select column_names table*/
			$query = "SELECT * FROM column_names WHERE column_names.name = '$table[0]'";
                                                              
			$result = mysql_query($query) or die ("column_names Invalid Select " . mysql_error());
        
			$num = mysql_num_rows($result);
        
			if($num == 0)
			{
				/* Insert column name and long name into the table if doesn't exist */
				$query = "INSERT into column_names values ('$table[0]', 
														   '$table[1]')";
																  
				mysql_query($query) or die ("column_names Invalid insert " . mysql_error());
			}
		}
	}
	
    mysql_close();

    include("init_disease_locations.php");
    include("init_disease_data.php");
    include("init_climate_data.php");
    
?>
</body>
</html>